﻿Namespace Utils
    Public NotInheritable Class SMOExt

        ''' <summary>
        ''' Uses the Smo object model to get a list of available Sql servers.
        ''' </summary>
        ''' <param name="refresh">true to force a refresh of the list; false read from Static instance if filled</param>
        ''' <returns>An ArrayList containing the names of the servers found.</returns>
        ''' <remarks>
        ''' The list is cached, so getting the list multiple time only uses the resources on the first call
        ''' unless refresh is set.
        ''' </remarks>
        Public Shared Function GetServerList(Optional ByVal refresh As Boolean = False) As ArrayList
            Static Dim serverList As ArrayList
            If serverList Is Nothing Or refresh Then
                Dim dtb As DataTable = Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers()
                Try
                    serverList = New ArrayList
                    If dtb.Rows.Count > 0 Then
                        For Each dr As DataRow In dtb.Rows
                            serverList.Add(dr("Name"))
                        Next
                    End If
                    Return serverList
                Catch
                    Return Nothing
                Finally
                    If dtb IsNot Nothing Then dtb.Dispose()
                End Try
            Else
                Return serverList
            End If
        End Function


        ''' <summary>
        ''' Return a datatable of Network Server for the given computer's name
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function GetNetworkServerTable() As DataTable

            Dim dtb As DataTable = SmoApplication.EnumAvailableSqlServers(False)
            Try

                If dtb.Rows.Count > 0 Then

                    Return dtb
                End If

            Catch
                Return Nothing
            Finally
                If dtb IsNot Nothing Then dtb.Dispose()



            End Try

            ' no warring on NullException
            Return Nothing

        End Function

        ''' <summary>
        ''' Return a list of Network Server and verison  for the given computer's name using WMI or all server
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function GetNetworkServerVersion() As ArrayList
            Dim aconnection As Microsoft.SqlServer.Management.Common.ServerConnection

            Dim serverList As ArrayList
            Try
                Dim mc As ManagedComputer = New Wmi.ManagedComputer
                Dim SerInst As Wmi.ServerInstanceCollection = mc.ServerInstances
                'Dim aserverIPAdrss As Wmi.ServerIPAddress
                serverList = New ArrayList
                Dim aserver As Microsoft.SqlServer.Management.Smo.Server


                If Not SerInst Is Nothing AndAlso SerInst.Count > 0 Then
                    For Each asi As ServerInstance In SerInst
                        aconnection = New Microsoft.SqlServer.Management.Common.ServerConnection
                        aconnection.ServerInstance = asi.Parent.Name
                        If Not String.IsNullOrEmpty(asi.Name) Then


                            aconnection.ServerInstance = aconnection.ServerInstance & "\" & asi.Name
                        End If
                        aconnection.Connect()

                        aserver = New Microsoft.SqlServer.Management.Smo.Server(aconnection)
                        Dim alistvalue As String = String.Format("{0},  {1},  {2}, {3}", aserver.Name, aserver.VersionString, aserver.ServiceName, aserver.Edition)
                        serverList.Add(alistvalue)

                        'Console.WriteLine(aserver.Information.Version)


                        aconnection.Disconnect()
                        aconnection = Nothing

                    Next
                End If
                Return serverList
            Catch ex As Exception
                Return Nothing
            Finally

            End Try


        End Function

        ''' <summary>
        ''' 
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function GetNetworkServerList() As ArrayList
            Static Dim serverList As ArrayList
            Try
                Dim mc As ManagedComputer = New Wmi.ManagedComputer
                Dim SerInst As Wmi.ServerInstanceCollection = mc.ServerInstances
                serverList = New ArrayList

                If Not SerInst Is Nothing AndAlso SerInst.Count > 0 Then
                    For Each asi As ServerInstance In SerInst
                        Console.WriteLine(asi.Name)
                        serverList.Add(asi.Name)
                    Next
                End If
                Return serverList
            Catch
                Return Nothing
            Finally

            End Try


        End Function





        ''' <summary>
        ''' Return a list of Network Server for the given computer's name
        ''' </summary>
        ''' <param name="computerName"></param>
        ''' <param name="refresh"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function GetNetworkServerList(ByVal computerName As String, Optional ByVal refresh As Boolean = False) As ArrayList
            Static Dim serverList As ArrayList
            If serverList Is Nothing Or refresh Then
                Dim dtb As DataTable = Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers(False)
                Try
                    serverList = New ArrayList
                    If dtb.Rows.Count > 0 Then
                        For Each dr As DataRow In dtb.Rows
                            Dim alistvalue As String = String.Format("{0},  {1},  {2}, {3}", dr("Name").ToString, dr("Server").ToString, dr("Instance").ToString, dr("Version").ToString)
                            serverList.Add(alistvalue)
                        Next
                    End If
                    Return serverList
                Catch
                    Return Nothing
                Finally
                    If dtb IsNot Nothing Then dtb.Dispose()
                End Try
            Else
                Return serverList
            End If
        End Function

        ''' <summary>
        ''' Return a list of ONLY Local Server
        ''' </summary>
        ''' <param name="refresh"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function GetLocalServerList(Optional ByVal refresh As Boolean = False) As ArrayList
            Static Dim serverList As ArrayList
            If serverList Is Nothing Or refresh Then
                Dim dtb As DataTable = Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers(True)
                Try
                    serverList = New ArrayList
                    If dtb.Rows.Count > 0 Then
                        For Each dr As DataRow In dtb.Rows
                            serverList.Add(dr("Name"))
                        Next
                    End If
                    Return serverList
                Catch
                    Return Nothing
                Finally
                    If dtb IsNot Nothing Then dtb.Dispose()
                End Try
            Else
                Return serverList
            End If
        End Function


        ''' <summary>
        ''' Uses SQLDMO to get a list of database availble on the specified server.
        ''' </summary>
        ''' <param name="serverName">Name of server to connect to.</param>
        ''' <param name="isTrusted">true to use a trusted connection; false otherwise</param>
        ''' <param name="userName">Username to use if not using a trusted connection.</param>
        ''' <param name="password">Password to use if not using a trusted connection.</param>
        ''' <returns>An ArrayList containing the names of the databases found.</returns>
        ''' <remarks></remarks>
        Public Shared Function GetDatabaseList(ByVal serverName As String, ByVal isTrusted As Boolean, ByVal userName As String, ByVal password As String) As ArrayList
            Dim alDBList As ArrayList = New ArrayList
            Dim svrCon As Microsoft.SqlServer.Management.Common.ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection(serverName, userName, password)
            svrCon.LoginSecure = isTrusted
            Dim svr As Microsoft.SqlServer.Management.Smo.Server = New Microsoft.SqlServer.Management.Smo.Server(svrCon)
            Dim db As Database

            Try
                For Each db In svr.Databases
                    If Not db.Name Is Nothing Then
                        alDBList.Add(db.Name)
                    End If
                Next
            Catch
                alDBList = Nothing
            End Try
            Return alDBList
        End Function

        ''' <summary>
        ''' Parse an SqLStatement Like in Sql Management Studio
        ''' </summary>
        ''' <param name="sqlStatement"></param>
        ''' <remarks></remarks>
        Public Shared Sub ParseSQLStatement(ByVal sqlStatement As String)

            Dim svrCon As Microsoft.SqlServer.Management.Common.ServerConnection = New Microsoft.SqlServer.Management.Common.ServerConnection(Base.AppManager.DbServerName, Base.AppManager.UserName, Base.AppManager.Password)
            svrCon.LoginSecure = Base.AppManager.IsTrustedConnection
            Dim svr As Microsoft.SqlServer.Management.Smo.Server = New Microsoft.SqlServer.Management.Smo.Server(svrCon)
            Dim db As Database
            db = svr.Databases(Base.AppManager.DbName)
            'Run a Transact-SQL command on the database.
            db.ExecuteNonQuery(sqlStatement, Microsoft.SqlServer.Management.Common.ExecutionTypes.NoCommands)

        End Sub
    End Class
End Namespace
